import openpyxl


wb = openpyxl.load_workbook(filename=r"F:\PythonFile\Define\pythonProject1\mine\baidu.xlsx")

sheet = wb["人员管理"]

countt = sheet.max_row-1
print("共有员工", countt,"人")

list1 = ["134","135","136","137","138","139","147","150","151","152","157","158","159","178","182","183","184","187","188","1703","1705","1706"]
list2 = ["130","131","132","145","155","156","175","176","185","186","1704","1708","1707","1709","171"]
list3 = ["133","149","153","173","177","180","181","189","1700","1701","1702"]
c1 = 0
c2 = 0
c3 = 0

for rall in sheet["F"][1:]:
    dat = rall.value[:3]
    if dat == "170":
        dat = rall.value[:4]
    if dat in list1:
        c1 = c1 + 1
    elif dat in list2:
        c2 = c2 + 1
    else:
        c3 = c3 + 1
print("办移动卡的有",c1,"人，办联通卡的人有",c2,"人，办电信卡的有",c3,"人")
man = 0
wman = 0
for rall in sheet["I"][1:]:
    dat = rall.value
    if dat == "男":
        man = man + 1
    else:
        wman = wman + 1
print("男员工有",man,"人，女员工有",wman,"人")

n45 = 0
for rall in sheet["H"][1:]:
    dat = rall.value
    if dat > 45:
        n45 = n45 + 1
    else:
        continue
print("年龄大于45岁的有",n45,"人")

big8 = 0
lit3 = 0
for rall in sheet["L"][1:]:
    dat = rall.value
    if dat > 8000:
        big8 = big8 + 1
    elif dat < 3000:
        lit3 = lit3 + 1
    else:
        continue
print("薪资超过8k的有",big8,"人，薪资低于3k的有",lit3,"人")

cm = "传媒公司"
cmc = 0
for rall in sheet["N"][1:]:
    dat = rall.value
    if cm in dat:
        cmc = cmc + 1
    else:
        continue
print("去传媒公司的人有",cmc,"个")

hlj = "黑龙江"
bj = "北京"
fj = "福建"
sc = "四川"
gwc = 0
for rall in sheet["J"][1:]:
    dat = rall.value
    if hlj in dat or bj in dat or fj in dat or sc in dat:
        gwc = gwc + 1
    else:
        continue
print("高危地区的人有",gwc,"个")

